Some database systems provide fine-grain access control at an individual row level and/or at an individual column level. Rather than opening up an entire table to any individual user who gains privileges on all rows in the table, row-level security restricts access to specific rows in a table. The result is that any individual user sees a completely different set of data from the same table or view—only the data that person is authorized to see. For example, a manager of a department (e.g., dept_num=50) may be authorized to view privileged information (such as social security numbers) relating to employees in that department, but may be prevented from viewing the same type of privileged information relating to another department (i.e., dept_num !=50). Similarly, a non-management employee (e.g., emp_num=12345) may be authorized to view privileged information relating to the employee, but may be prevented from viewing the same type of privileged information relating to another employee (i.e., emp_num !=12345).
One approach to provide row-level access control is to automatically generate, based on a user-submitted query, an equivalent query that uses conditional expressions based on a user's credentials (or user's security context). For example, a field “SSN” might be a protected column (i.e., privileged information) in a table “Employee”. Under such circumstances, one or more values (in certain rows of the Employee table) in that column cannot be accessed unless the user who submits the original query is authorized to do so.
Now, a user such as the manager of the present example may issue a query (Q1) as follows:
SELECT name, SSN, phone_number, addressFROM EmployeeWHERE SSN LIKE “123%”;After this “Q1” query is submitted, a new query that is equivalent to the “Q1” query as subject to an access control policy may be automatically created. Specifically, for the manager, the text that indicates the protected column “SSN”, as specified in the above query, turns into a case statement (i.e., a type of conditional expression) as indicated in an equivalent query (EQ1) as follows:
SELECT name, SSN, phone_number, addressFROM (SELECT name,(case when dept_num = 50 then SSN elsenull) SSN, phone_number, addressFROM Employee)WHERE SSN like “123%”;This equivalent query may actually be submitted in place of the “Q1” query to yield a query result that only displays values of the “SSN” column for employees in the department (i.e., dept_num=50) whose privileged information the manager is authorized to view. In this manner, each user (including, for example, the non-management employee previously discussed) may have a different equivalent query, thereby obtaining a different result, relative to the same “Q1” based on each user's security context.
However, once certain columns in the original queries are substituted by conditional expressions in equivalent queries, a database system may lose track of hints contained in original queries. These hints would ordinarily enable the database system to select a less costly query plan, such as a query using an existing index to access rows in an efficient manner. For instance, in the above example, there may be an index on the “SSN” column of the “Employee” table. Since the “WHERE” clause in the original, unmodified query includes the “SSN” column, the database system would be likely to access rows of the table using the index on the “SSN” column, if the original, unmodified query were submitted for execution and if the Employee table were not subject to an access control policy. However, since the original query is replaced by a new equivalent query to enforce the access control policy, even though the equivalent query still refers to an “SSN” field, the “SSN” field in the new equivalent query is a mere alias for a case statement, not for any column in a underlying table. As a result, the equivalent query may be executed with a full table scan on the Employee table, causing significant inefficiency.
Therefore, a better mechanism, which would better optimize queries that are subject to access control policies, is needed.